CREATE OR REPLACE PACKAGE EQUIPMENTTOOLS IS

--===============================================================
-- Type declarations
--===============================================================

--===============================================================
-- Function and Procedure declarations
--===============================================================

FUNCTION VerifyEquipmentTools (Lot IN VARCHAR2
                               ,Equipment IN VARCHAR2)
RETURN VARCHAR2
;

procedure VerifyEjactor2078 (Lot IN VARCHAR2 ,ToolName IN VARCHAR2 ,Result out BOOLEAN)
;

procedure VerifyEjactor (Lot IN VARCHAR2 ,ToolName IN VARCHAR2 ,Result out BOOLEAN)
;

procedure VerifyMoldCap (Lot IN VARCHAR2 ,ToolName IN VARCHAR2 ,Result out BOOLEAN)
;

procedure VerifyGrindWheel (Lot IN VARCHAR2 ,ToolName IN VARCHAR2,Result out BOOLEAN)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY EQUIPMENTTOOLS AS

--=====================
-- VerifyEquipmentTools
--=====================
FUNCTION VerifyEquipmentTools (Lot IN VARCHAR2
                               , Equipment IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    vLot VARCHAR2(100) := Lot;
    vEquipment VARCHAR2(100) := Equipment;
    vResult Boolean := false;
    Toolcount number:= 0;
    vcurrentstep VARCHAR2(100);

/*  Modify Date:2011-08-24 by henry-ding
    1--change table A_EQUIPMENTTOOLSDETAILS to resourcedef
    2--change table ToolType to resourcefamily
  --=====================
    -- Filter EquipmentTools
    -- Bryan Wang
    --=====================
    CURSOR curToolType IS
    SELECT Distinct
        B.equipmenttoolname ToolName ,
        C.tooltypename ToolType,
        UPPER(B.description) Description
    FROM
        A_EQUIPMENTTOOLSDETAILS A,
        A_Tools B,
        ResourceFamily C,
        RESOURCEDEF D
    WHERE
        A.equipmenttoolid=B.equipmenttoolid
        AND B.tooltypeid =C.tooltypeid
        AND A.resourceid =D.resourceid
        AND A.turnon =1
        AND D.resourcename = vEquipment;*/
        
    --=====================
    -- Filter EquipmentTools
    -- Henry Ding
    --=====================
    CURSOR curToolType IS
    SELECT Distinct
        a.resourcename ToolName ,
        C.Resourcefamilyname ToolType,
        UPPER(a.description) Description
    FROM
        RESOURCEDEF A,
        A_Tools B,
        ResourceFamily C,
        RESOURCEDEF D,
        Productionstatus ps,
        resourcestatuscode rs
    WHERE
        A.Resourceid=B.Toolid
        AND a.resourcefamilyid=c.resourcefamilyid
        AND b.parentid =D.resourceid
        and ps.productionstatusid=a.productionstatusid
        and ps.statusid=rs.resourcestatuscodeid
        and b.turnon =1
        AND D.resourcename = vEquipment;

     --=====================
    -- Local variables
    --=====================
    v_ToolName Resourcedef.Resourcename%TYPE;
    v_ToolType resourcefamily.resourcefamilyname%TYPE;
    v_ToolDescription Resourcedef.description%TYPE;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN

    --=====================
    -- Get Lot Information
    --=====================
    OPEN curToolType;
    LOOP

        FETCH curToolType INTO v_ToolName,v_ToolType,v_ToolDescription;
        EXIT WHEN curToolType%NOTFOUND;

        IF v_ToolType = 'MOLD CAP' THEN

            VerifyMoldCap(Lot,v_ToolName,vResult);

        ElsIF v_ToolType = 'EJECTOR' THEN

            IF v_ToolDescription= 'NEEDLE' THEN
                vResult := true;
                EXIT;
            ELSE
                select distinct d.specname into vcurrentstep
                from container a,currentstatus b,spec c,specbase d
                where a.currentstatusid=b.currentstatusid and b.specid=c.specid
                and c.specbaseid=d.specbaseid and a.containername=Lot;

                IF vcurrentstep='2078' THEN
                   VerifyEjactor2078(Lot,v_ToolName,vResult);
                ELSE
                   VerifyEjactor(Lot,v_ToolName,vResult);
                END IF;
            END IF;


        ElsIF v_ToolType = 'GRIND WHEEL' THEN
/*             
              modify date:2011-8-24
                  1--change table A_EQUIPMENTTOOLSDETAILS to resourcedef
                  2--change table ToolType to resourcefamily
                         
               SELECT
                    count(B.equipmenttoolname) INTO Toolcount
                FROM
                    A_EQUIPMENTTOOLSDETAILS A,
                    A_EQUIPMENTTOOL B,
                    A_TOOLTYPE C,

                    RESOURCEDEF D
                WHERE
                    A.equipmenttoolid=B.equipmenttoolid
                    AND B.tooltypeid =C.tooltypeid
                    AND A.resourceid =D.resourceid
                    AND A.turnon =1
                    AND D.resourcename = vEquipment
                    AND C.tooltypename = 'GRIND WHEEL';*/
                
                SELECT
                    count(a.resourcename) INTO Toolcount
                 FROM
                    RESOURCEDEF A,
                    A_Tools B,
                    ResourceFamily C,
                    RESOURCEDEF D,
                    Productionstatus ps,
                    resourcestatuscode rs
                WHERE
                    A.Resourceid=B.Toolid
                    AND a.resourcefamilyid=c.resourcefamilyid
                    AND b.parentid =D.resourceid
                    and ps.productionstatusid=a.productionstatusid
                    and ps.statusid=rs.resourcestatuscodeid
                    and b.turnon =1
                    AND D.resourcename = vEquipment
                    AND C.Resourcefamilyname = 'GRIND WHEEL';

            IF Toolcount= 2 THEN
                VerifyGrindWheel(Lot,v_ToolName,vResult);
            ELSE
                RAISE_APPLICATION_ERROR (-20005, 'EquipmentToolsHandler_E0911:'||'You should add 2 Grind Wheels in this equipment');
                EXIT;
            END IF;
        END IF;

        if vResult=false then
            EXIT;
        end if;

    END LOOP;

    CLOSE curToolType;

    IF vResult = false THEN
        RAISE_APPLICATION_ERROR (-20005, 'EquipmentToolsHandler_E0911:'|| 'EQUIPMENTTOOLS is null or EQUIPMENTTOOLS can not be used in this'|| vLot);
    ELSE
        RETURN ('0');
    END IF;

EXCEPTION
    WHEN eExit THEN
        RETURN ('EquipmentTools.VerifyEquipmentTools->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('EquipmentTools.VerifyEquipmentTools->' || SQLERRM);
END VerifyEquipmentTools;


--=====================
-- VerifyEjactor
--=====================
procedure VerifyEjactor2078 (Lot IN VARCHAR2 , ToolName IN VARCHAR2,Result out BOOLEAN) IS

    --=====================
    -- Input parameter variables
    --=====================
    vLot VARCHAR2(100) := Lot;
    vToolName VARCHAR2(100) := ToolName;

    --=====================
    -- Filter the parameters
    --=====================
        CURSOR curEjActorPart IS
        SELECT   a.parametervalue
        FROM a_productattributes a, a_productdetails b, a_productattrname c
        WHERE a.productattributesid = b.productattributesid AND a.productattrnameid = c.productattrnameid
              AND b.productname IN (SELECT SUBSTR (mat_ptb.productname, 1, 11)
              FROM container co INNER JOIN a_scheduledata sd
                   ON sd.scheduledataid = co.scheduledataid
                   INNER JOIN product pt ON sd.productid = pt.productid
                   INNER JOIN a_productbomlist ptbl
                   ON ptbl.productid = pt.productid
                   INNER JOIN a_productbommateriallist mat_list
                   ON mat_list.productbomid = ptbl.bomid
                   INNER JOIN spec sp ON mat_list.specid = sp.specid
                   INNER JOIN specbase spb ON spb.specbaseid = sp.specbaseid
                   INNER JOIN product mat_pt
                   ON mat_pt.productid = mat_list.materialpartid
                   INNER JOIN productbase mat_ptb
                   ON mat_ptb.productbaseid = mat_pt.productbaseid
                   INNER JOIN a_materialtype mt
                   ON mat_pt.materialtypeid = mt.materialtypeid
             WHERE mt.materialtypename LIKE 'CONTROLLERDIE%'
               AND co.containername = Lot
               AND spb.specname = '2078')
        AND c.productattrnamename = 'EJECTOR PART'
        ORDER BY a.parametervalue;

    --=====================
    -- Local variables
    --=====================
    v_EjactorPart a_productattributes.parametervalue%TYPE;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN

    --=====================
    -- Verify Ejactor
    --=====================
    Result := TRUE;
    OPEN curEjActorPart;
    LOOP

        FETCH curEjActorPart INTO v_EjactorPart;
        EXIT WHEN curEjActorPart%NOTFOUND;


        IF SUBSTR (v_EjactorPart , -5) = SUBSTR (vToolName,1,5) THEN
            Result := TRUE;
            EXIT;
        ELSE
            Result := FALSE;
            EXIT;
        END IF;

    END LOOP;

    CLOSE curEjActorPart;


EXCEPTION
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,'EquipmentTools.verifyejactor2078(lot,toolname,result);->' || Sqlerrm);
END VerifyEjactor2078;


--=====================
-- VerifyEjactor
--=====================
procedure VerifyEjactor (Lot IN VARCHAR2 , ToolName IN VARCHAR2,Result out BOOLEAN) IS

    --=====================
    -- Input parameter variables
    --=====================
    vLot VARCHAR2(100) := Lot;
    vToolName VARCHAR2(100) := ToolName;

    --=====================
    -- Filter the parameters
    --=====================
    CURSOR curEjActorPart IS
    SELECT
        a.parametervalue
    FROM
        a_productattributes a,
        a_productdetails b,
        A_PRODUCTATTRNAME c
    WHERE
        a.productattributesid = b.productattributesid
        AND A.productattrnameid =c.productattrnameid
        AND trim(b.productname) IN (SELECT DISTINCT substr(atr.waferpart,1,11)
                              FROM container con, a_lotattributes atr
                              WHERE con.containerid = atr.containerid AND con.containername= Lot)
        AND c.productattrnamename ='EJECTOR PART'
        ORDER BY a.parametervalue;


    --=====================
    -- Local variables
    --=====================
    v_EjactorPart a_productattributes.parametervalue%TYPE;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN

    --=====================
    -- Verify Ejactor
    --=====================
    Result := TRUE;
    OPEN curEjActorPart;
    LOOP

        FETCH curEjActorPart INTO v_EjactorPart;
        EXIT WHEN curEjActorPart%NOTFOUND;


        IF SUBSTR (v_EjactorPart , -5) = SUBSTR (vToolName,1,5) THEN
            Result := TRUE;
            EXIT;
        ELSE
            Result := FALSE;
            EXIT;
        END IF;

    END LOOP;

    CLOSE curEjActorPart;


EXCEPTION
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,'EquipmentTools.verifyejactor(lot,toolname,result);->' || Sqlerrm);
END VerifyEjactor;


--=====================
-- VerifyMoldCap
--=====================
procedure VerifyMoldCap (Lot IN VARCHAR2 , ToolName IN VARCHAR2 ,Result out BOOLEAN) IS

    --=====================
    -- Input parameter variables
    --=====================
    vLot VARCHAR2(100) := Lot;
    vToolName VARCHAR2(100) := ToolName;
    vCount NUMBER := 0;

/*    --=====================
    -- Filter the parameters
    --=====================
    CURSOR curMoldCapValue IS
    SELECT
        A.PARAMETERVALUE,
        A.packagecategoryid
    FROM
        A_EQUIPMENTTOOLS A,
        A_EQUIPMENTTOOL B
    WHERE
        A.equipmenttoolid = B.equipmenttoolid
        AND B.equipmenttoolname = ToolName;*/
        
    --=====================
    -- Filter the parameters
    -- modify date:2011-8-24
    -- henry ding
    --   1--change table A_EQUIPMENTTOOLSDETAILS to resourcedef
    --   2--change table ToolType to resourcefamily
    --=====================
    CURSOR curMoldCapValue IS
    SELECT
        A.Objecttype PARAMETERVALUE,
        A.Objectcategory packagecategoryid
    FROM
        resourcefamily A,        
        resourcedef B

    WHERE
        A.Resourcefamilyid = B.Resourcefamilyid
        AND B.Resourcename = ToolName;

    --=====================
    -- Local variables
    --=====================
    v_MoldCapValue resourcefamily.Objecttype%TYPE;
    v_PackagecategoryID resourcefamily.Objectcategory%TYPE;


    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN

    --=====================
    -- Verify the Mold Cap
    --=====================
    Result := false;
    OPEN curMoldCapValue;
    LOOP

        FETCH curMoldCapValue INTO v_MoldCapValue,v_PackagecategoryID;
        EXIT WHEN curMoldCapValue%NOTFOUND;


        /*SELECT
            COUNT(*) INTO vCount
        FROM
            CONTAINER A,
            A_PROCESSSPECDETAILS B,
            A_PROCESSSPECDETAILSPARAMS C,
            PRODUCT D
        WHERE
            A.processspecid=B.processspecid
            AND B.processspecdetailsid=C.processspecdetailsid
            AND A.productid=D.productid
            AND C.paramname ='MOLD CAP'
            AND A.containername = vLot
            AND D.packagecategoryid = v_PackagecategoryID
            AND C.paramvalue LIKE v_MoldCapValue||'%';*/
            
/*        SELECT
            COUNT(*) INTO vCount
        FROM
            CONTAINER A,
            PRODUCT B
        WHERE
            A.productid=B.productid
            AND A.containername = vLot
            AND B.packagecategoryid = v_PackagecategoryID
            AND B.tfi LIKE v_MoldCapValue||'%';*/
            
        --modify date:2011-08-24
        --modify packagecatefory
        
        SELECT
            COUNT(*) INTO vCount
        FROM
            CONTAINER A,
            PRODUCT B
            left join productbase pb on pb.productbaseid=b.productbaseid,
            a_productdetails pd,            
            a_productattributes pt
            left join a_packagecategory pc
            on pt.packagecategoryid=pc.packagecategoryid
        WHERE
            A.productid=B.productid
            and pd.productname=pb.productname
            and pd.productattributesid=pt.productattributesid
            AND A.containername = vLot
            AND pc.packagecategoryname = v_PackagecategoryID
            AND pt.parametervalue LIKE v_MoldCapValue||'%';

        IF vCount>=1 THEN
            Result := true;
            EXIT;
        END IF;

    END LOOP;

    CLOSE curMoldCapValue;

EXCEPTION
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,'EquipmentTools.VerifyMoldCap(lot,toolname,result);->' || Sqlerrm);
END VerifyMoldCap;

--=====================
-- VerifyGrindWheel
--=====================
procedure VerifyGrindWheel (Lot IN VARCHAR2 , ToolName IN VARCHAR2,Result out BOOLEAN) IS

    --=====================
    -- Input parameter variables
    --=====================
    vLot VARCHAR2(100) := Lot;
    vToolName VARCHAR2(100) := ToolName;

    --=====================
    -- Filter the parameters
    --=====================
    CURSOR curDiethicknes IS
    SELECT
        B.diethickness
    FROM
        CONTAINER A,PRODUCT B
    WHERE
        A.productid=B.productid
        AND A.containername = Lot;

    --=====================
    -- Local variables
    --=====================
    v_Diethicknes PRODUCT.diethickness%TYPE;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN

    --=====================
    -- Verify the grind wheel
    --=====================
    Result := false;
    OPEN curDiethicknes;
    LOOP

        FETCH curDiethicknes INTO v_Diethicknes;
        EXIT WHEN curDiethicknes%NOTFOUND;

        if TO_NUMBER(v_Diethicknes)<64 then
           if vToolName= 'BT300(TSK)' or vToolName= 'VV3A(TSK)' then
               Result := true;
           else
               Result := false;
           end if;
        elsif TO_NUMBER(v_Diethicknes)>=64 and TO_NUMBER(v_Diethicknes)<152 then
           if vToolName= 'BT300(TSK)' or vToolName= 'PW006(TSK)' then
               Result := true;
           else
               Result := false;
           end if;
        elsif TO_NUMBER(v_Diethicknes)>=152 then
           if vToolName= 'BT300(TSK)' or vToolName= 'VV3A(TSK)' or vToolName= 'BT300(Disco)' or vToolName= 'VV3A(Disco)' then
               Result := true;
           else
               Result := false;
           end if;
        end if;

    END LOOP;

    CLOSE curDiethicknes;

EXCEPTION
    When Eexit Then
      Rollback;
    When Eapperror Then
      Rollback;
      Raise_Application_Error(-20005, Sqlerrm);
    When Others Then
      Rollback;
      Raise_Application_Error(-20005,'EquipmentTools.VerifyGrindWheel(lot,toolname,result);->' || Sqlerrm);
END VerifyGrindWheel;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
